package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;

/**
 * A class used for managing SM_ERROR_LOG
 */
public class SMErrorLogDAO {
	
	/** a database of this SM_ERROR_LOG */
    private static Database db = new Database();
    private static SMErrorLogDAO voErrorLog = null;

    private SMErrorLogDAO() {
    	
    }
    
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public static SMErrorLogDAO getInstance() {
    	if(voErrorLog == null) {
    		voErrorLog = new SMErrorLogDAO();
         }
         return voErrorLog;
    }

    /**
     * a method for searching all SM_ERROR_LOG in database
     * @return all SM_ERROR_LOG
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM SM_ERROR_LOG ORDER BY ERROR_DATE,ERROR_TIME ASC";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    
    /**
     * a method for adding a SM_ERROR_LOG into database
     * @param SM_ERROR_LOG to add
     */
    public void addLog(int pnErrorId, String paProgramCode, String paErrorDate, String paErrorTime,
			int pnUserId, String paClassName, String paErrorDescription, String paUrlAddress, String paAgentBrowser) {
        String vaSql = "INSERT INTO sm_error_log" + " (error_id, program_code, error_date, error_time, "
        		+ "user_id, class_name, error_description, url_address, agent_browser)" 
        		+ " VALUES(sm_error_log_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            db.insert(vaSql,paProgramCode,paErrorDate,paErrorTime,pnUserId,paClassName,paErrorDescription,paUrlAddress,paAgentBrowser);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    public List<Map<String, Object>> findAllJoinUserLogin(int pnStartT,int pnAmountT) {
    	int vnStart = pnStartT+1;
    	int vnEnd = vnStart+pnAmountT-1;
    	if(vnEnd==-1){
    		String vaSql = "" +
            		"SELECT * " +
            		"FROM SM_ERROR_LOG s " +
            		"LEFT OUTER JOIN USER_LOGIN u " +
            		"ON s.USER_ID = u.USER_ID " +
            		"ORDER BY s.ERROR_DATE ASC,s.ERROR_TIME ASC ";
            List<Map<String, Object>> result = db.queryList(vaSql);
            if (result != null) {
                return result;
            } else {
                return null;
            }
    	}
    	
        String vaSql = "" +
        		"SELECT * " +
        		"FROM " +
        		"( " +
        		"SELECT ROWNUM r,sm.* " +
        		"FROM " +
        		"( " +
        		"SELECT * " +
        		"FROM SM_ERROR_LOG s " +
        		"LEFT OUTER JOIN USER_LOGIN u " +
        		"ON s.USER_ID = u.USER_ID " +
        		"ORDER BY s.ERROR_DATE ASC,s.ERROR_TIME ASC " +
        		") sm " +
        		"WHERE ROWNUM <="+vnEnd+" "+
        		") " +
        		"WHERE r>="+vnStart+" ";
        
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    public int countFindAllJoinUserLogin() {

    	String vaSql = "SELECT COUNT(*) FROM ( " +
    			"SELECT * " +
        		"FROM SM_ERROR_LOG s " +
        		"LEFT OUTER JOIN USER_LOGIN u " +
        		"ON s.USER_ID = u.USER_ID " +
    			") ";
    	
        Map<String, Object> result = db.querySingle(vaSql);
        if (result != null) {
        	return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    public List<Map<String,Object>> searchData(String paStartDate,String paEndDate,String paUserName,int pnStart,int pnAmount){
    	
    	int vnStart = pnStart+1;
    	int vnEnd = vnStart+pnAmount-1;
    	
    	if(vnEnd==-1){
    		String vaSql = "" +
            		"SELECT * " +
            		"FROM SM_ERROR_LOG s " +
            		"LEFT OUTER JOIN USER_LOGIN u " +
            		"ON s.USER_ID = u.USER_ID " +
            		"WHERE 1=1 ";
        	if(!paStartDate.equals("")){
        		vaSql+="AND s.ERROR_DATE>= "+paStartDate+" ";
        	}
        	if(!paEndDate.equals("")){
        		vaSql+="AND s.ERROR_DATE<= "+paEndDate+" ";
        	}
        	if(!paUserName.equals("")){
        		vaSql+="AND u.USER_NAME LIKE  (LOWER('%"+paUserName+"%')) ";  
        	}
        	vaSql+="ORDER BY ERROR_DATE ASC , ERROR_TIME ASC ";
        	
            List<Map<String, Object>> result = db.queryList(vaSql);
            if (result != null) {
                return result;
            } else {
                return null;
            }
    	}
    	
 
    	String vaSql = "" +
        		"SELECT * " +
        		"FROM " +
        		"( " +
        		"SELECT ROWNUM r,sm.* " +
        		"FROM " +
        		"( " +
        		"SELECT * " +
        		"FROM SM_ERROR_LOG s " +
        		"LEFT OUTER JOIN USER_LOGIN u " +
        		"ON s.USER_ID = u.USER_ID " +
        		"WHERE 1=1 ";
    	if(!paStartDate.equals("")){
    		vaSql+="AND ERROR_DATE>="+paStartDate+" ";
    	}
    	if(!paEndDate.equals("")){
    		vaSql+="AND ERROR_DATE<="+paEndDate+" ";
    	}
    	if(!paUserName.equals("")){
    		vaSql+="AND u.USER_NAME LIKE  (LOWER('%"+paUserName+"%')) "; 
    	}
    	
    	vaSql+="ORDER BY ERROR_DATE ASC , ERROR_TIME ASC "+
        		") sm " +
        		"WHERE ROWNUM <="+vnEnd+" "+
        		") " +
        		"WHERE r>="+vnStart+" ";
    	
    	List<Map<String, Object>> result = db.queryList(vaSql);
    	  if (result != null) {
              return result;
          } else {
              return null;
          }
    }
    
    public int countSearchData(String paStartDate,String paEndDate,String paUserName){
    	
		String vaSql = " " +
				"SELECT COUNT(*) FROM ( " +
        		"SELECT * " +
        		"FROM SM_ERROR_LOG s " +
        		"LEFT OUTER JOIN USER_LOGIN u " +
        		"ON s.USER_ID = u.USER_ID " +
        		"WHERE 1=1 ";
    	if(!paStartDate.equals("")){
    		vaSql+="AND ERROR_DATE>="+paStartDate+" ";
    	}
    	if(!paEndDate.equals("")){
    		vaSql+="AND ERROR_DATE<="+paEndDate+" ";
    	}
    	if(!paUserName.equals("")){
    		vaSql+="AND u.USER_NAME LIKE  (LOWER('%"+paUserName+"%')) "; 
    	}
    	vaSql+= ") ";
    	Map<String, Object> result = db.querySingle(vaSql);
        if (result != null) {
        	return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    
    
}
